﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Text.RegularExpressions;
using System.Collections;
using System.IO;

namespace DtaOutputRenamerUI
{

    public partial class FormMain : Form
    {
        public FormMain()
        {
            InitializeComponent();

            // Custom Initialization
            InitializeForm();
        }

        #region UI Helpers
        /// <summary>
        /// Wire up event handlers, set defaults, etc.
        /// </summary>
        private void InitializeForm()
        {
            this.textBoxSql.KeyUp += new KeyEventHandler(textBoxSql_KeyUp);
        }

        void textBoxSql_KeyUp(object sender, KeyEventArgs e)
        {
            // CTRL-A = Select all text
            if ((e.Control) && (e.KeyCode == Keys.A))
            {
                textBoxSql.SelectAll();
            }

        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void openToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string filecontents = string.Empty;

            if(openFileDialog.ShowDialog() == DialogResult.OK)
            {
                filecontents = LoadStringFromFile(openFileDialog.FileName);
                textBoxSql.Text = filecontents;
            }
        }

        private void buttonRename_Click(object sender, EventArgs e)
        {
            string sql = string.Empty;

            sql = textBoxSql.Text.Trim();

            sql = RenameStatistics(sql);

            sql = RenameIndexes(sql);

            textBoxSql.Text = sql;

        }

        private void buttonStripGos_Click(object sender, EventArgs e)
        {
            string sql = string.Empty;

            sql = textBoxSql.Text.Trim();

            sql = StripGoStatements(sql);

            textBoxSql.Text = sql;
        }

        #endregion

        #region Main Methods
        /// <summary>
        /// Strip any 'Go' statements that appear on a line by themselves from
        /// the SQL.
        /// </summary>
        /// <param name="sql">SQL Script to update</param>
        /// <returns>Updated SQL Script</returns>
        private string StripGoStatements(string sql)
        {
            string strippedSql = sql;

            strippedSql = strippedSql.Replace(Environment.NewLine + "go" + Environment.NewLine, Environment.NewLine);

            return strippedSql;
        }

        /// <summary>
        /// Rename indexes based on Clustered/Non-Clustered, schema, table, 
        /// columns, and included columns.
        /// 
        /// New names may be longer than allowed by SQL.
        /// </summary>
        /// <param name="sql">SQL Script to update</param>
        /// <returns>Updated SQL Script</returns>
        private string RenameIndexes(string sql)
        {
            string renamedSql = string.Empty;
            string tableSchema = string.Empty;
            string tableName = string.Empty;
            string nonClustered = string.Empty;
            StringBuilder newIndexName = null;
            List<string> columnNames = null;
            List<string> columnIncludeNames = null;

            Dictionary<string, string> replacementPairs = new Dictionary<string, string>();

            string regex = "CREATE\\s(?<NonClustered>NON)?CLUSTERED\\sINDEX\\s\\[(?<IndexName>.*?)\\].*?ON\\s\\[(?<S" 
                    + "chemaName>.*?)\\]\\.\\[(?<TableName>.*?)\\].*?\\((?<ColumnList>.*?)\\)\\s*?(?:INCLUDE\\s\\((?<Inc" 
                    + "ludeList>.*?)\\))??\\s*?(?:WITH\\s*?\\(.*?\\)\\s*?ON\\s\\[.*?\\])";
            RegexOptions options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline)
                        | RegexOptions.IgnoreCase);
            Regex reg = new Regex(regex, options);

            // For each match, extract the table schema/name/columns, and create
            // a new INDEX name for it
            foreach (Match m in reg.Matches(sql))
            {
                newIndexName = new StringBuilder();
                tableName = m.Groups["TableName"].Value;
                tableSchema = m.Groups["SchemaName"].Value;
                nonClustered = m.Groups["NonClustered"].Value;
                columnNames = GetColumnNames(m.Groups["ColumnList"].Value);
                columnIncludeNames = GetColumnNames(m.Groups["IncludeList"].Value);

                newIndexName.Append("IX");

                // Add a 'C' if it is Clustered index
                if (nonClustered.Length == 0)
                {
                    newIndexName.Append("C");
                }

                newIndexName.Append(Properties.Settings.Default.ColumnSeparator + tableSchema + Properties.Settings.Default.ColumnSeparator + tableName);

                foreach (string columnName in columnNames)
                {
                    newIndexName.Append(Properties.Settings.Default.ColumnSeparator + columnName);
                }

                if (columnIncludeNames.Count > 0)
                {
                    newIndexName.Append(Properties.Settings.Default.ColumnSeparator + "I");
                }

                foreach (string columnName in columnIncludeNames)
                {
                    newIndexName.Append(Properties.Settings.Default.ColumnSeparator + columnName);
                }
                
                // Add the old and new name to a Dictionary for later replacement
                replacementPairs.Add(
                     QuoteSqlObject(m.Groups["IndexName"].Value)
                    , QuoteSqlObject(newIndexName.ToString()));
            }

            // It's later... do the replacements
            renamedSql = PerformReplacements(sql, replacementPairs);

            return renamedSql;
        }

        /// <summary>
        /// Renames CREATE STATISTICS statements in a SQL script based on the
        /// Table Schema/Name and columns
        /// </summary>
        /// <param name="sql">SQL Script to update</param>
        /// <returns>Updated SQL Script</returns>
        private string RenameStatistics(string sql)
        {
            string renamedSql = string.Empty;
            string tableSchema = string.Empty;
            string tableName = string.Empty;
            StringBuilder newStatisticsName = null;
            List<string> columnNames = null;
            Dictionary <string, string> replacementPairs = new Dictionary<string,string>();

            // Regex to match CREATE STATISTICS statements in SQL
            string regex = "CREATE\\sSTATISTICS\\s\\[(?<StatisticsName>.*?)\\].*?ON\\s\\[(?<SchemaName>.*?)\\]\\.\\[(?<TableName>.*?)\\].*?\\((?<ColumnList>.*?)\\)";
            RegexOptions options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline) 
                        | RegexOptions.IgnoreCase);
            Regex reg = new Regex(regex, options);

            // For each match, extract the table schema/name/columns, and create
            // a new STATISTIC name for it
            foreach (Match m in reg.Matches(sql))
            {
                newStatisticsName = new StringBuilder();
                tableName = m.Groups["TableName"].Value;
                tableSchema = m.Groups["SchemaName"].Value;
                columnNames = GetColumnNames(m.Groups["ColumnList"].Value);

                newStatisticsName.Append("Stat" + Properties.Settings.Default.ColumnSeparator
                    + tableSchema + Properties.Settings.Default.ColumnSeparator + tableName);

                foreach (string columnName in columnNames)
                {
                    newStatisticsName.Append(Properties.Settings.Default.ColumnSeparator + columnName);
                }

                // Add the old and new name to a Dictionary for later replacement
                replacementPairs.Add(
                     QuoteSqlObject(m.Groups["StatisticsName"].Value)
                    ,QuoteSqlObject(newStatisticsName.ToString()));
            }

            // It's later... do the replacements
            renamedSql = PerformReplacements(sql, replacementPairs);

            return renamedSql;
        }
        #endregion

        #region Helper Methods
        /// <summary>
        /// Extract the column names (anything quoted using '[' and ']') from a string
        /// into a List object.
        /// </summary>
        /// <param name="columnList">Text containing one or more quoted ('[',']') columns</param>
        /// <returns>List of column names</returns>
        private List<string> GetColumnNames(string columnList)
        {
            List<string> columnNames = new List<string>();

            string regex = "\\[(?<ColumnName>.*?)\\]";
            RegexOptions options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline)
                        | RegexOptions.IgnoreCase);
            Regex reg = new Regex(regex, options);
            
            foreach (Match m in reg.Matches(columnList))
            {
                columnNames.Add(m.Groups["ColumnName"].Value);
            }

            return columnNames;
        }

        /// <summary>
        /// This method will perform multiple replacements in a string. 
        /// </summary>
        /// <param name="input">String to perform the replacements on</param>
        /// <param name="replacementPairs">Dectionary where the Keys will be replaced with the Values</param>
        /// <returns>String with replacements made</returns>
        private string PerformReplacements(string input, Dictionary<string, string> replacementPairs)
        {
            string replacedInput = input;

            foreach (KeyValuePair<string, string> replacementPair in replacementPairs)
            {
                replacedInput = replacedInput.Replace(
                     replacementPair.Key.ToString()
                    ,replacementPair.Value.ToString());
            }

            return replacedInput;

        }

        /// <summary>
        /// Put quoting characters ('[' and ']') around a string.
        /// </summary>
        /// <param name="sqlObject">String to quote</param>
        /// <returns>Quoted string</returns>
        private string QuoteSqlObject(string sqlObject)
        {
            string ret = string.Empty;

            ret = "[" + sqlObject + "]";

            return ret;
        }

        /// <summary>
        /// Read the contents of a file into a string
        /// </summary>
        /// <param name="filename">Filename to read</param>
        /// <returns>Contents of the file</returns>
        private string LoadStringFromFile(string filename)
        {
            string fileContents = string.Empty;

            using (StreamReader sr = new StreamReader(filename))
            {
                fileContents = sr.ReadToEnd();
            }

            return fileContents;
        }
        #endregion

    }
}
